﻿import os
import sys
try:
    import openpyxl
    print('Loading openpyxl succeeded')
except ModuleNotFoundError:
    print('For the first time, install the openpyxl library now.')
    os.system('pip install openpyxl')
try:
    import pandas
    print('Loading pandaas succeeded')
except ModuleNotFoundError:
    print('For the first time, install pandas library now.')
    os.system('pip install -i https://pypi.tuna.tsinghua.edu.cn/simple pandas')
import time
import threading
from time import sleep
from openpyxl import load_workbook
from openpyxl import worksheet

fliename = sys.argv[1]

wb = load_workbook(fliename,data_only=True)
sheet = wb.sheetnames
ws = wb[sheet[0]]
f = open('检查结果.txt','w')
#ws = wb.active
lock = threading.Lock()

os.system("taskkill /f /im notepad.exe 1>nul 2>nul")

print('''Checking, it takes about 1 minute...
Notice: Link correctness needs to be checked by yourself!
\n''')

def Count():
    for i in range(1 , 100):
        print("\r", end="")
        print("Progress: {}%: ".format(i), "▓" * (i // 4), end="")
        sys.stdout.flush()
        time.sleep(0.6)


#Item : Check sheet name
def CSN():
    #Check for extra sheets
    if len(wb.sheetnames) != 1:
        #print("存在多余工作表，请删除！")
        f.write("存在多余工作表，请删除！")
    #Check hidden sheets
    for i in wb.worksheets:
        if i.sheet_state == "hidden":
            #print("存在隐藏工作表！")
            f.write('\n存在隐藏工作表！')


#Item: Check illegal characters
def CIC():
    #All
    #Find 0 value cells and output coordinates
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=1, max_col=93):
        for cell in row:
            if cell.value == 0:
               #print("0值所在单元格：" + cell.coordinate )
               f.write("\n0值所在单元格：" + cell.coordinate )
    #All
    #Find '-' value cells and output coordinates
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=1, max_col=93):
        for cell in row:
            if cell.value == "-":
               #print("单字符“-”所在单元格：" + cell.coordinate)
               f.write("\n单字符“-”所在单元格：" + cell.coordinate)
    #Find ' value cells and output coordinates
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=1, max_col=93):
        for cell in row:
            if str(cell.value).find('\'') != -1:
               #print("带引号的单元格坐标：" + cell.coordinate)
               f.write("\n带引号的单元格坐标：" + cell.coordinate)
            elif str(cell.value).find('’') != -1:
                #print("带引号的单元格坐标：" + cell.coordinate)
                f.write("\n带引号的单元格坐标：" + cell.coordinate)
            elif str(cell.value).find('‘') != -1:
                #print("带引号的单元格坐标：" + cell.coordinate)
                f.write("\n带引号的单元格坐标：" + cell.coordinate)
            elif str(cell.value).find('\'') != -1:
                #print("带引号的单元格坐标：" + cell.coordinate)
                f.write("\n带引号的单元格坐标：" + cell.coordinate)
    #Find formula errors and output coordinates
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=1, max_col=93):
        for cell in row:
            if cell.value == '#N/A':
               #print("公式计算错误所在单元格：" + cell.coordinate)
               f.write("\n公式计算错误所在单元格：" + cell.coordinate)
            elif cell.value == '#REF!':
                #print("公式计算错误所在单元格：" + cell.coordinate)
                f.write("\n公式计算错误所在单元格：" + cell.coordinate)


#Item : : Checking for formatting errors
def CFFE():
    #Find cells that end with spaces and output coordinates
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=3, max_col=93):
        for cell in row:
            if len(str(cell.value)) != 0:
                if (str(cell.value)[-1]).find(' ') != -1:
                   #print("以空格结束的单元格：" + cell.coordinate)
                   f.write("\n以空格结束的单元格：" + cell.coordinate)
    #Check whether the color number area is too long
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=69, max_col=69):
        for cell in row:
            if len(str(cell.value)) > 30:
                #print("色号区域超过30个字符：" + cell.coordinate)
                f.write("\n色号区域超过30个字符：" + cell.coordinate)
    #Find the plate number with suffix space and output coordinates
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=67, max_col=67):
        for cell in row:
            if str(cell.value).find(' ') != -1:
               #print("带空格的板块编号坐标：" + cell.coordinate)
               f.write("\n带空格的板块编号坐标：" + cell.coordinate)
    #Check whether the number of columns is correct
    for row in ws.iter_rows(min_row=4, max_row=4, min_col=1, max_col=100):
        for cell in row:
            if cell.value == "图纸链接":
                if cell.column != 93:
                    #print('表格列数异常，请检查！')
                    f.write('\n表格列数异常，请检查！')


#Item: Numerical anomaly
def NA():
    #column-F
    #Check whether the production quantity is greater than 1
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=6, max_col=6):
        for cell in row:
            if cell.value != None:
                if cell.value != 1:
                    #print("生产数量大于1（此处录mest前修改）：" + cell.coordinate)
                   f.write("\n生产数量大于1（此处录mest前修改）：" + cell.coordinate)
    #column-S
    #Inventory attribute is blank
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=2, max_col=2):
        for cell in row:
            if cell.value != None:
                U = int(cell.row)
                for row in ws.iter_rows(min_row=U, max_row=U, min_col=19, max_col=19):
                    for cell in row:
                        if cell.value != "销售,生产":
                            #print("请确定材料用量或计量单位是否正确：" + cell.coordinate)
                            f.write("\n存货属性一栏为空：" + cell.coordinate)
    #column-BT                          
    #Material usage has null value
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=65, max_col=65):
        for cell in row:
            if cell.value != None:
                U = int(cell.row)
                for row in ws.iter_rows(min_row=U, max_row=U, min_col=72, max_col=72):
                    for cell in row:
                        if cell.value == None:
                            #print("请确定材料用量或计量单位是否正确：" + cell.coordinate)
                            f.write("\n请确定材料用量或计量单位是否正确：" + cell.coordinate)
    #column-BU
    #UOM has null value
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=65, max_col=65):
        for cell in row:
            if cell.value != None:
                U = int(cell.row)
                for row in ws.iter_rows(min_row=U, max_row=U, min_col=73, max_col=73):
                    for cell in row:
                        if cell.value == None:
                            #print("请确定材料用量或计量单位是否正确：" + cell.coordinate)
                            f.write("\n请确定材料用量或计量单位是否正确：" + cell.coordinate)

#Item: Duplicate and missing check
def DAMC():
    #Find duplicate plate numbers and output coordinates（Object must be in text form）
    last_row = ws.max_row
    names = []
    for B in range(6, last_row+1):
        names.append(ws.cell(row=B,column=2).value)
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=2, max_col=2):
        for cell in row:
            if names.count(str(cell.value)) == 2:
               #print("重复编号所在单元格：" + cell.coordinate)
               f.write("\n重复编号所在单元格：" + cell.coordinate)

#Column by column inspection
def CBCI():
    #column-A(1)
    #Check whether the number is consistent with the plate
    U = int()
    for row in ws.iter_rows(min_row=6+U, max_row=30000, min_col=2, max_col=2):
        for cell in row:
            if cell.value != None:
                W = cell.value
                U = int(cell.row)
                return U
                for row in ws.iter_rows(min_row=U+1, max_row=30000, min_col=1, max_col=1):
                    for cell in row:
                        if cell.value != None:
                            V = int(cell.row)
                            for row in ws.iter_rows(min_row=U, max_row=V-1, min_col=1, max_col=1):
                                if str(cell.value).find(W) != -1:
                                    #print("带空格的板块编号坐标：" + cell.coordinate)
                                    f.write("\n首列错误编号所在单元格：" + cell.coordinate)
                                    break

def Main():
    CSN()
    CIC()
    CFFE()
    NA()
    DAMC()
    CBCI()

threads = []
t1 = threading.Thread(target = Main)
threads.append(t1)
t2 = threading.Thread(target = Count)
threads.append(t2)

if __name__ == "__main__":
    for t in threads:
        t.start()
    for t in threads:
        t.join()

print("\r检查进度：100%\n")

#print("\n以上为检查结果，请按结果在表格内修改。")
f.write("\n以上为检查结果，请按结果在表格内修改。")
f.close()

os.system("notepad 检查结果.txt")


Help = input('Enter "help" for help：')
if Help == 'help':
    print("参考意见：1.确定不是导入类别错误，如散料当成成品导入。")
    print("参考意见：2.多次导入失败，且未显示错误原因，不排除是系统延迟，请静置10分钟后在MEST检查是否已导入系统。")
    print("参考意见：3.刚进行导入立即提示错误，确定文件格式没有错误后，可将表格拆分后再尝试导入。")
    input("\n按任意键退出")



#废弃代码
'''#判断图纸链接正确性
#Judge the correctness of drawing link
last_row = ws.max_row
List = []
for CO in range(6, last_row+1):
    List.append(ws.cell(row=CO,column=2).value)
if str(List.remove("")).find(NO) == -1:
    print("图纸链接错误，请检查！")'''

#安装要求：
#版本要求：python3.5以上版本，
#    官网：https://www.python.org/
#    教程：https://www.python.org/
#库要求：
#    pandas：
#           win+r,cmd窗口，键入pip install pandas
#           镜像：pip install -i https://pypi.tuna.tsinghua.edu.cn/simple pandas
#    openpyxl:
#           win+r,cmd窗口，键入pip install openpyxl

#Log:
#Ver:1.3
#Date:2022.12.04
#New Features:   1)Integrate to the function set
#*****
#Ver:1.2
#Date:2022.11.23
#New Features:   1)Display running progress
#*****
#Ver:1.1
#Date:2022.11.11
#New Features:   1)Retrieve hidden sheets
#*****
#Write by r11-s9edn5hhl

#plan1 :  sure the link of drawings format is right
